package cn.mfeg.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import cn.mfeg.controllers.CollegesUserController;
import cn.mfeg.models.ProjectExpert;
import cn.mfeg.services.ProjectExpertService;

public class UploadExcelServlet extends HttpServlet{
	private static final long serialVersionUID = 1L;
	Logger log=Logger.getLogger(CollegesUserController.class);
	ApplicationContext applicationContext;
	ProjectExpertService projectExpertService;
	
	public void init() throws ServletException {
		applicationContext = new ClassPathXmlApplicationContext("spring-servlet.xml");
		projectExpertService = (ProjectExpertService) applicationContext.getBean(ProjectExpertService.class);
	}
	
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.setContentType("text/html;charset=utf-8");
		PrintWriter writer = resp.getWriter();
		try {
			String path = req.getServletContext().getRealPath("/downloadFiles");
			ServletFileUpload ss = new ServletFileUpload(new DiskFileItemFactory());// 使用系统默认的临时目录
			List<FileItem> list = ss.parseRequest(req);
			// 由于一个文件，和一个表单项目是一组
			for (FileItem item : list) {
				if (item.isFormField()) {// 普通的表表单项目 request.getparams()
					String value = item.getString(req.getCharacterEncoding());
					log.info(value);
				} else {
					String fileName = item.getName();
					fileName = fileName.substring(fileName.lastIndexOf("\\") + 1);
					File file = new File(path, fileName);
					item.write(file);//存放到磁盘中
					FileInputStream fis = new FileInputStream(file);
					Workbook workbook = new HSSFWorkbook(fis);
					/** 获取sheet */
					Sheet sheet = workbook.getSheetAt(0);
					/** 获取sheet的列数 */
					//int columnNum = sheet.getRow(0).getPhysicalNumberOfCells();
					/** 获取文件有多少行 */
					int rowNum = sheet.getLastRowNum()+1;
					//获取第二行所有数据
					for(int i=1;i<rowNum;i++){
						String projectId = getCellStringValue(sheet.getRow(i),0);//项目ID
						String expertId = getCellStringValue(sheet.getRow(i),2);//专家ID
						String appraise = getCellStringValue(sheet.getRow(i),11);//评价
						String comment = getCellStringValue(sheet.getRow(i),12);//评语
						String expertName = getCellStringValue(sheet.getRow(i),4);//专家姓名
						//检验该专家是否存在
						ProjectExpert projectExpert = new ProjectExpert();
						projectExpert.setProjectid(projectId);
						projectExpert.setExpertid(expertId);
						if (!projectExpertService.checkExpertByProjectIdExpertId(projectExpert)) {//不存在
							workbook.close();
							writer.println("专家"+expertName+"不存在！");
							return;
						}
						//将数据加入数据库
						projectExpert.setAppraise(appraise);
						projectExpert.setComment(comment);
					    if (!projectExpertService.insertAppraiseCommentByPidEid(projectExpert)) {//插入失败
						    workbook.close();
						    writer.println("在导入专家"+expertName+"的信息时出现错误！");
						    return;
					    }
					}
					item.delete();
					workbook.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			writer.println("导入失败！");
			return;
		}
		 writer.println("导入成功！");
	}
	public final static String getCellStringValue(Row row, int i) {
		Cell cell = row.getCell(i);
		if (cell == null) {
			return "";
		}
		cell.setCellType(Cell.CELL_TYPE_STRING);
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
}
